package cn.schina.dbfw.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import cn.schina.dbfw.dao.DaoConn;
import cn.schina.dbfw.dao.JDBCConnection;

public class QueryParamsDao {

	ArrayList<QueryParam> queryParams = new ArrayList<QueryParam>();
	JDBCConnection conn = null;
	String sql;
	int baselineid;
	int first_flag = 0;
	public ArrayList<QueryParam> getQueryParams() throws SQLException
	{
		conn = DaoConn.createDBConnection();
		PreparedStatement statement = conn.getConnection()
			.prepareStatement("select 1 as param_code,UPPER(db_user) as param_value " +
					"from sessionlog_event sess " +
					"where sess.source_id=? and " +
					"sess.db_server=? and " +
					"sess.connect_time between ? and ? and " +
					"sess.db_instance_name=? " +
					"group by sess.db_user " +
					"union " +
					"select 2 as param_code,UPPER(db_client) as param_value " +
					"from sessionlog_event sess " +
					"where sess.source_id=? and " +
					"sess.db_server=? and " +
					"sess.connect_time between ? and ? and " +
					"sess.db_instance_name=? " +
					"group by sess.db_client " +
					"union " +
					"select 3 as param_code,UPPER(clientmac) as param_value " +
					"from sessionlog_event sess " +
					"where sess.source_id=? and " +
					"sess.db_server=? and " +
					"sess.connect_time between ? and ? and " +
					"sess.db_instance_name=? " +
					"group by sess.clientmac " +
					"union " +
					"select 4 as param_code,UPPER(program_nm) as param_value " +
					"from sessionlog_event sess,ac_dbapps " +
					"where sess.source_id=? and " +
					"sess.db_server=? and " +
					"sess.connect_time between ? and ? and " +
					"sess.db_instance_name=? and " +
					"sess.program_nm=ac_dbapps.app_name and " +
					"ac_dbapps.app_type=1 " +
					"group by sess.program_nm " +
					"union " +
					"select 5 as param_code,UPPER(program_nm) as param_value " +
					"from sessionlog_event sess,ac_dbapps " +
					"where sess.source_id=? and " +
					"sess.db_server=? and " +
					"sess.connect_time between ? and ? and " +
					"sess.db_instance_name=? and ( " +
					"(sess.program_nm=ac_dbapps.app_name and ac_dbapps.app_type=2) or  " +
					"sess.program_nm not in (select ac_dbapps.app_name from ac_dbapps)) " +
					"group by sess.program_nm"
					);
		// 第1组参数 
		statement.setInt(1, 1);
		statement.setString(2, "192.168.1.24");
		statement.setString(3, "2012-12-20");
		statement.setString(4, "2012-12-26");
		statement.setString(5, "orcl24");
		// 第2组参数 
		statement.setInt(6, 1);
		statement.setString(7, "192.168.1.24");
		statement.setString(8, "2012-12-20");
		statement.setString(9, "2012-12-26");
		statement.setString(10, "orcl24");
		// 第3组参数 
		statement.setInt(11, 1);
		statement.setString(12, "192.168.1.24");
		statement.setString(13, "2012-12-20");
		statement.setString(14, "2012-12-26");
		statement.setString(15, "orcl24");
		// 第4组参数 
		statement.setInt(16, 1);
		statement.setString(17, "192.168.1.24");
		statement.setString(18, "2012-12-20");
		statement.setString(19, "2012-12-26");
		statement.setString(20, "orcl24");
		// 第5组参数 
		statement.setInt(21, 1);
		statement.setString(22, "192.168.1.24");
		statement.setString(23, "2012-12-20");
		statement.setString(24, "2012-12-26");
		statement.setString(25, "orcl24");
		
		ResultSet resultSet = statement.executeQuery();
		while(resultSet.next())
		{
			QueryParam _queryparam = new QueryParam();
			_queryparam.setParam_code(resultSet.getInt("param_code"));
			_queryparam.setParam_value(resultSet.getString("param_value"));
			queryParams.add(_queryparam);
		}
		statement.close();
		return queryParams;
	}
	
	public ArrayList<QueryParam> getQueryParams(DBWithDbfw p_dbwithdbfw,DateParam p_date) throws SQLException
	{
		Iterator<Dbfw> it;
		conn = DaoConn.createDBConnection();
		//数据库用户参数
		sql = "select 1 as param_code,UPPER(db_user) as param_value " +
				"from sessionlog_event sess ";
		//where sess.source_id in (0,1) and
		sql = sql + "where sess.source_id in (0";
		it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
		}
		sql = sql + ") and ";
		//sess.db_server='192.168.1.24' and
		sql = sql + "sess.db_server='"+p_dbwithdbfw.getDb_address()+"' and ";
		//sess.connect_time between '2012-12-20' and '2012-12-26' and
		sql = sql + "sess.connect_time between '"+p_date.startDate+"' and '"+p_date.endDate+"' and ";
		//UPPER(sess.db_instance_name) in ('',UPPER('orcl24'))
		sql = sql + "UPPER(sess.db_instance_name)='"+p_dbwithdbfw.getName()+"' ";
		sql = sql + "group by sess.db_user ";
		sql = sql + " union ";
		//客户端IP参数
		sql = sql + "select 2 as param_code,UPPER(db_client) as param_value " +
				"from sessionlog_event sess ";
		//where sess.source_id in (0,1) and
		sql = sql + "where sess.source_id in (0";
		it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
		}
		sql = sql + ") and ";
		//sess.db_server='192.168.1.24' and
		sql = sql + "sess.db_server='"+p_dbwithdbfw.getDb_address()+"' and ";
		//sess.connect_time between '2012-12-20' and '2012-12-26' and
		sql = sql + "sess.connect_time between '"+p_date.startDate+"' and '"+p_date.endDate+"' and ";
		//UPPER(sess.db_instance_name) in ('',UPPER('orcl24'))
		sql = sql + "UPPER(sess.db_instance_name)='"+p_dbwithdbfw.getName()+"' ";
		sql = sql + "group by sess.db_client ";
		sql = sql + " union ";
		//clientmac参数
		sql = sql + "select 3 as param_code,UPPER(clientmac) as param_value " +
				"from sessionlog_event sess ";
		//where sess.source_id in (0,1) and
		sql = sql + "where sess.source_id in (0";
		it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
		}
		sql = sql + ") and ";
		//sess.db_server='192.168.1.24' and
		sql = sql + "sess.db_server='"+p_dbwithdbfw.getDb_address()+"' and ";
		//sess.connect_time between '2012-12-20' and '2012-12-26' and
		sql = sql + "sess.connect_time between '"+p_date.startDate+"' and '"+p_date.endDate+"' and ";
		//UPPER(sess.db_instance_name) in ('',UPPER('orcl24'))
		sql = sql + "UPPER(sess.db_instance_name)='"+p_dbwithdbfw.getName()+"' ";
		sql = sql + "group by sess.clientmac ";
		sql = sql + " union ";
		//program_nm参数
		sql = sql + "select 4 as param_code,UPPER(program_nm) as param_value " +
				"from sessionlog_event sess,ac_dbapps ";
		//where sess.source_id in (0,1) and
		sql = sql + "where sess.source_id in (0";
		it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
		}
		sql = sql + ") and ";
		//sess.db_server='192.168.1.24' and
		sql = sql + "sess.db_server='"+p_dbwithdbfw.getDb_address()+"' and ";
		//sess.connect_time between '2012-12-20' and '2012-12-26' and
		sql = sql + "sess.connect_time between '"+p_date.startDate+"' and '"+p_date.endDate+"' and ";
		//UPPER(sess.db_instance_name) in ('',UPPER('orcl24'))
		sql = sql + "UPPER(sess.db_instance_name)='"+p_dbwithdbfw.getName()+"' and ";
		sql = sql + "sess.program_nm=ac_dbapps.app_name and " +
				"ac_dbapps.app_type=1 ";
		sql = sql + "group by sess.program_nm ";
		sql = sql + " union ";
		//program_nm(应用)
		sql = sql + "select 5 as param_code,UPPER(program_nm) as param_value " +
				"from sessionlog_event sess,ac_dbapps ";
		//where sess.source_id in (0,1) and
		sql = sql + "where sess.source_id in (0";
		it = p_dbwithdbfw.getDbfwArray().iterator();
		while(it.hasNext())
		{
			Dbfw tmp_dbfw = it.next();
			sql = sql + ","+tmp_dbfw.getInstid();
		}
		sql = sql + ") and ";
		//sess.db_server='192.168.1.24' and
		sql = sql + "sess.db_server='"+p_dbwithdbfw.getDb_address()+"' and ";
		//sess.connect_time between '2012-12-20' and '2012-12-26' and
		sql = sql + "sess.connect_time between '"+p_date.startDate+"' and '"+p_date.endDate+"' and ";
		//UPPER(sess.db_instance_name) in ('',UPPER('orcl24'))
		sql = sql + "UPPER(sess.db_instance_name)='"+p_dbwithdbfw.getName()+"' and ";
		sql = sql + "((sess.program_nm=ac_dbapps.app_name and ac_dbapps.app_type=2) or " +
				"sess.program_nm not in (select ac_dbapps.app_name from ac_dbapps)) ";
		sql = sql + "group by sess.program_nm ";
		//System.out.println(sql);
		PreparedStatement statement = conn.getConnection()
			.prepareStatement(sql);
		
		ResultSet resultSet = statement.executeQuery();
		while(resultSet.next())
		{
			QueryParam _queryparam = new QueryParam();
			_queryparam.setParam_code(resultSet.getInt("param_code"));
			_queryparam.setParam_value(resultSet.getString("param_value"));
			queryParams.add(_queryparam);
		}
		statement.close();
		return queryParams;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		ArrayList<QueryParam> queryParams = new ArrayList<QueryParam>();
		QueryParamsDao _queryparamdao;
		DBWithDbfw p_dbwithdbfw = new DBWithDbfw();
		DateParam p_date = new DateParam();
		Dbfw _dbfw;
		p_dbwithdbfw.setId(1);
		p_dbwithdbfw.setName("orcl24");
		p_dbwithdbfw.setDbtype("1");
		p_dbwithdbfw.setDbversion("10020001");
		p_dbwithdbfw.setDialect(1);
		p_dbwithdbfw.setDb_address("192.168.1.24");
		p_dbwithdbfw.setDb_port(1521);
		p_dbwithdbfw.setName_forprotected("test");
		_dbfw = new Dbfw();
		_dbfw.setInstid(1);
		_dbfw.setName("dbfw24");
		_dbfw.setBaselineid(174);
		_dbfw.setAddress("192.168.1.24");
		p_dbwithdbfw.addDbfwForDB(_dbfw);
		p_date.setStartDate("2012-12-20");
		p_date.setEndDate("2012-12-26");
		_queryparamdao = new QueryParamsDao();
		try {
			queryParams = _queryparamdao.getQueryParams(p_dbwithdbfw,p_date);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}
